USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

declare @databaseName sysname = 'TEST'
declare @sql varchar(500)

if DB_ID(@databaseName) is null
begin 
	select @sql = N'CREATE DATABASE ' + @databaseName;  
	execute(@sql)
end
go

USE [TEST]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGenerateFakeData]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGenerateFakeData]
GO

CREATE PROCEDURE [dbo].[spGenerateFakeData]
AS
BEGIN
	
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.FinalizedIndicators') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[FinalizedIndicators](
			[Id] uniqueidentifier NOT NULL,
			[CompanyId] uniqueidentifier NOT NULL,
			[Name] nvarchar(max) NOT NULL,
			[Number] nvarchar(50) NOT NULL,
			[Description] nvarchar(1500) NULL,
			[Score] int NOT NULL,
			[Outlook] int NULL,
			[Tickedboxes] nvarchar(max) NULL,
			[Comments] nvarchar(max) NULL,
			[Sources] nvarchar(max) NULL

		) ON [PRIMARY]
	END
	
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.FinalizedSources') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[FinalizedSources](
			[Id] uniqueidentifier NOT NULL,
			[CompanyId] uniqueidentifier NOT NULL,
			[Code] nvarchar(max) NOT NULL,
			[Text] nvarchar(max) NOT NULL,
			[IndicatorsNumbers] nvarchar(max) NULL
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.IndicatorTemplates') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[IndicatorTemplates](
		[IndicatorId] [uniqueidentifier] NOT NULL,
		[IndicatorName] [nvarchar](max) NOT NULL,
		[IndicatorNumber] [nvarchar](max) NOT NULL,
		[IndicatorSortKey] [int] NOT NULL,
		[IssueName] [nvarchar](max) NOT NULL,
		[IssueNumber] int NOT NULL
		) ON [PRIMARY]
	END
	
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.IndicatorTemplateTickboxes') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[IndicatorTemplateTickboxes](
		[IndicatorId] [uniqueidentifier] NOT NULL,
		[TickboxId] [uniqueidentifier] NOT NULL,
		[Number] [nvarchar](max) NOT NULL,
		[Text] [nvarchar](max) NOT NULL
		) ON [PRIMARY]
	END
	
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.IndicatorTemplateAnswerCategories') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[IndicatorTemplateAnswerCategories](
		[IndicatorId] [uniqueidentifier] NOT NULL,
		[Code] [char](1) NOT NULL,
		[Text] [nvarchar](max) NOT NULL,
		[Score] int NOT NULL
		) ON [PRIMARY]
	END


	TRUNCATE TABLE [dbo].[IndicatorTemplates]
	TRUNCATE TABLE [dbo].[IndicatorTemplateTickboxes]
	TRUNCATE TABLE [dbo].[IndicatorTemplateAnswerCategories]
	TRUNCATE TABLE [dbo].[FinalizedIndicators]
	TRUNCATE TABLE [dbo].[FinalizedSources]

	DECLARE @CompanyIds TABLE
	(
		CompanyId uniqueidentifier
	)
	
	-- create fake indicators

	DECLARE @index INT
	DECLARE @indicator INT
	DECLARE @CompanyId uniqueidentifier

	SET @index = 1
	WHILE (@index <= 50)
	BEGIN
		SET @CompanyId = cast(replicate('0', 8 - len(ltrim(rtrim(str(@index))))) + ltrim(rtrim(str(@index))) + '-0000-0000-0000-000000000000' as uniqueidentifier)

		INSERT INTO @CompanyIds
		SELECT @CompanyId AS CompanyId

		SET @index = @index + 1
        
		SET @indicator = 1
		WHILE (@indicator <= 50)
		BEGIN
		    INSERT INTO [dbo].[FinalizedIndicators]
				   ([Id]
				   ,[CompanyId]
				   ,[Name]
				   ,[Number]
				   ,[Description]
				   ,[Score]
				   ,[Outlook]
				   ,[Tickedboxes]
				   ,[Comments]
				   ,[Sources])
			 VALUES
				   (NEWID()
				   ,@CompanyId
				   ,'Indicator ' + STR(@indicator)
				   ,'N.' + STR(@indicator)
				   ,'Description ' + STR(@indicator)
				   ,10
				   ,0
				   ,NULL
				   ,NULL
				   ,NULL)

			SET @indicator = @indicator + 1
		END
	END

	-- create fake indicator templates
	
	DECLARE @issue int
	DECLARE @IndicatorId uniqueidentifier
	DECLARE @tickbox1 int
	DECLARE @tickbox2 int
	DECLARE @TickboxId uniqueidentifier
	DECLARE @answer int

	SET @issue = 1
	WHILE (@issue <= 6)
	BEGIN
		SET @indicator = 1
		WHILE (@indicator <= 9)
		BEGIN
			SET @IndicatorId = cast('0000000' + ltrim(rtrim(str(@issue))) + '-0000-0000-0000-00000000000' + ltrim(rtrim(str(@indicator))) as uniqueidentifier)

		    INSERT INTO [dbo].[IndicatorTemplates]
				   ([IndicatorId]
				   ,[IndicatorName]
				   ,[IndicatorNumber]
				   ,[IndicatorSortKey]
				   ,[IssueName]
				   ,[IssueNumber])
			 VALUES
				   (@IndicatorId
				   ,'Indicator ' + ltrim(rtrim(str(@issue))) + '.' + ltrim(rtrim(str(@indicator)))
				   ,ltrim(rtrim(str(@issue))) + '.' + ltrim(rtrim(str(@indicator)))
				   ,@issue * 100 + @indicator
				   ,'Issue ' + ltrim(rtrim(str(@issue)))
				   ,@issue)

			SET @answer = 1
			WHILE (@answer <= 4)
			BEGIN			
				INSERT INTO [dbo].[IndicatorTemplateAnswerCategories]
						([IndicatorId]
						,[Code]
						,[Text]
						,[Score])
					VALUES
						(@IndicatorId
						,'C'
						,'Answer ' + ltrim(rtrim(str(@answer)))
						,@answer * 25)
						   			
				SET @answer = @answer + 1
			END

			SET @tickbox1 = 1
			WHILE (@tickbox1 <= 9)
			BEGIN
				SET @tickbox2 = 1
				WHILE (@tickbox2 <= 9)
				BEGIN
					SET @TickboxId = cast('0000000' + ltrim(rtrim(str(@issue))) + '-000' + ltrim(rtrim(str(@tickbox1))) + '-000'+ ltrim(rtrim(str(@tickbox2))) +'-0000-00000000000' + ltrim(rtrim(str(@indicator))) as uniqueidentifier)
				
					INSERT INTO [dbo].[IndicatorTemplateTickboxes]
						   ([IndicatorId]
						   ,[TickboxId]
						   ,[Number]
						   ,[Text])
					 VALUES
						   (@IndicatorId
						   ,@TickboxId
						   ,ltrim(rtrim(str(@tickbox1))) + '.' + ltrim(rtrim(str(@tickbox2)))
						   ,'Tickbox ' + ltrim(rtrim(str(@tickbox1))) + '.' + ltrim(rtrim(str(@tickbox2)))
						   )
					
					SET @tickbox2 = @tickbox2 + 1
				END
				
				SET @tickbox1 = @tickbox1 + 1
			END

			SET @indicator = @indicator + 1
		END

		SET @issue = @issue + 1
	END

	-- create fake sources
	
	DECLARE @source INT

	SET @index = 1
	WHILE (@index <= 5000)
	BEGIN
		SET @CompanyId = cast(replicate('0', 8 - len(ltrim(rtrim(str(@index))))) + ltrim(rtrim(str(@index))) + '-0000-0000-0000-000000000000' as uniqueidentifier)

		SET @source = 1
		WHILE (@source <= 10)
		BEGIN
		    INSERT INTO [dbo].[FinalizedSources]
				   ([Id]
				   ,[CompanyId]
				   ,[Code]
				   ,[Text]
				   ,[IndicatorsNumbers])
			 VALUES
				   (NEWID()
				   ,@CompanyId
				   ,'I.' + ltrim(rtrim(str(@source)))
				   ,'Source ' + ltrim(rtrim(str(@source)))
				   ,'G.1.1, G.1.2, G.1.3')

			SET @source = @source + 1
		END
	
		SET @index = @index + 1
	END

	SELECT * FROM @CompanyIds 

END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetIndicatorsByCompanyIds]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetIndicatorsByCompanyIds]
GO

CREATE PROCEDURE [dbo].[spGetIndicatorsByCompanyIds]
	@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)

	SELECT FinalizedIndicators.[Id]
		  ,[CompanyId]
		  ,[Name]
		  ,[Number]
		  ,[Description]
		  ,[Score]
		  ,[Outlook]
		  ,[Tickedboxes]
		  ,[Comments]
		  ,[Sources]
	FROM [dbo].[FinalizedIndicators] AS FinalizedIndicators
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = FinalizedIndicators.CompanyId

END

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetSourcesByCompanyIds]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetSourcesByCompanyIds]
GO

CREATE PROCEDURE [dbo].[spGetSourcesByCompanyIds]
	@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)

	SELECT FinalizedSources.Id
		  ,[CompanyId]
		  ,[Code]
		  ,[Text]
		  ,[IndicatorsNumbers]
	FROM [dbo].[FinalizedSources] AS FinalizedSources
	INNER JOIN @SelectedCompanies AS SelectedCompanies ON SelectedCompanies.Id = FinalizedSources.CompanyId

END
GO


